package DBLayer;

import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;
/**
 * DBProduct.java
 * @author Brian Lassen
 * @version 22. Marts 2012
 */
public class DBProduct implements IFDBProduct
{
    private Connection con;
    
    public DBProduct()
    {
        con = DBConnection.getInstance().getDBcon();
    }
    
    public ArrayList<Product> getAllProducts(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    
    public Product findProduct(String name, boolean retriveAssociation)
    {
        String wClause = " name = " + name + "";
        return singleWhere(wClause, retriveAssociation);
    }
    
        public Product searchProductId(int attValue, boolean retriveAssociation)
    {
        String wClause = "id = " + attValue + "";
        System.out.println("Search Product: " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
    
    public Product searchProductName(String proName, boolean retriveAssociation)
    {
        String wClause = "Name = " + proName + "";
        System.out.println("Search product: " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
    
    public int createProduct(Product prod)
    {
        int nextId = GetMax.getMaxId("Select max(id) from product");
        nextId = nextId + 1;
        System.out.println("next id = " +  nextId);
        
       int rc = -1;
       String query="INSERT INTO Product(id,name,purchaseprice,salesprice,rentprice,countryoforigin,minstock,supid,type,clotingsize,color,eqtype,description,fabric,calibre)  VALUES(" +
            nextId  + ",'"  + 
            prod.getName()  + "'," + 
            prod.getPurchasePrice() + "," + 
            prod.getSalesPrice() + "," + 
            prod.getRentPrice() + ",'" + 
            prod.getCountryOfOrigin() + "'," + 
            prod.getMinStock() + "," +
            prod.getSupplyerid() + 
       		prod.getType() + "," +
       		prod.getClothingsize() + "," +
       		prod.getColor() + "," +
       		prod.getEqtype() + "," +
       		prod.getDescription() + "," +
       		prod.getFabric() + "," +
       		prod.getCalibre() + ")";
           
      System.out.println("insert : " + query);                          
      try{
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }   
        catch(Exception ex){
            System.out.println("Insert exception in product db: "+ex);
       }
       return(rc);
    }
    
    public int updateProduct(Product prod)
    {
        Product prodObj = prod;
        int rc=-1;
      
        String query="UPDATE Product SET " +
             "id = " + prodObj.getId() + ", " +
             "Purchase price =" + prodObj.getPurchasePrice() + ", " +
             "Sales price =" + prodObj.getSalesPrice() + ", " +
             "Rent price =" + prodObj.getRentPrice() + ", " +
             "Country of Origin ='" + prodObj.getCountryOfOrigin() + "', " +
             "Minumum stock =" + prodObj.getMinStock() + ", " +
             "Sup id =" + prodObj.getSupplyerid() + ", " +
             "WHERE name = '" + prodObj.getName() + "," +        
             "Product type =" + prod.getType() + "," +
             "Clothing size =" + prod.getClothingsize() + "," +
             "Color =" + prod.getColor() + "," +
             "Equitment type =" + prod.getEqtype() + "," +
             "Description =" + prod.getDescription() + "," +
             "Fabric =" + prod.getFabric() + "," +
             "Calibre =" + prod.getCalibre() + "'";
        
                System.out.println("Update query:" + query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
                      
            stmt.close();
        }
        catch(Exception ex)
        {
            System.out.println("Update exception in product db: "+ex);
        }
        return(rc);
    }
    
    public int deleteProduct(int id)
    {
       int rc=-1;
        String query="DELETE FROM Product WHERE id = " +
                id + "";
                System.out.println(query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception ex)
        {
            System.out.println("Delete exception in product db: "+ex);
        }
        return(rc);
    }
    
    private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<Product> list = new ArrayList<Product>();   
        
        String query =  buildQuery(wClause);
  
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            
            while( results.next() )
            {
                Product prodObj = new Product();
                prodObj = buildProduct(results);   
                list.add(prodObj); 
            }
            stmt.close();
        }
        catch(Exception e)
        {
            System.out.println("Query exception - select: "+e);
            e.printStackTrace();
        }
        return list;
    }
    
    private Product singleWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        Product prodObj = new Product();
        String query =  buildQuery(wClause);
        System.out.println(query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            
            if( results.next() )
            {
                prodObj = buildProduct(results);
                stmt.close();
            }
        
        }   
        catch(Exception e)
        {
            System.out.println("Query exception: "+e);
        }
        return prodObj;
    }

    private String buildQuery(String wClause)
    {
        String query="SELECT * FROM Product";
        
        if (wClause.length()>0)
            query=query+" WHERE "+ wClause;
            
        return query;
    }

    private Product buildProduct(ResultSet results)
      {   
        Product prodObj = new Product();
          try
          {
                prodObj.setId(results.getInt(1));
                prodObj.setName(results.getString(2));
                prodObj.setPurchasePrice(results.getInt(3));
                prodObj.setSalesPrice(results.getInt(4));
                prodObj.setRentPrice(results.getInt(5));
                prodObj.setCountryOfOrigin(results.getString(6));
                prodObj.setMinStock(results.getInt(7));
                prodObj.setSupplyerid(results.getInt(8));
                prodObj.setType(results.getString(9));
                prodObj.setClothingsize(results.getString(10));
           		prodObj.setColor(results.getString(11));
           		prodObj.setEqtype(results.getString(12));
           		prodObj.setDescription(results.getString(13));
           		prodObj.setFabric(results.getString(14));
           		prodObj.setCalibre(results.getString(15));
          }
         catch(Exception e)
         {
             System.out.println("error in building the Product object");
         }
         return prodObj;
      }
}